from datetime import datetime

from django.core.management import BaseCommand
from django.db import connection
from django.db.models import Q

from common.models.base_data import GroupInformationStatistics, MobileUserRecord, SpecialLineManagement


class Command(BaseCommand):
    """checkGroupStatistics：检查一对一表是否有缺失数据
       handle_mobiles_user_sql：更新手机用户统计
       handle_special_line_sql：更新专线统计
       handle_group_statistics：更新表中内部求和统计
    """
    def handle(self, *args, **kwargs):
        print('定时任务执行---handle_mobiles_user_sql')
        # now_date = datetime.today().date()
        # self.handle_mobiles_user(now_date)
        # self.handle_special_line_user(now_date)
        self.checkGroupStatistics()
        self.handle_mobiles_user_sql()
        self.handle_special_line_sql()
        self.handle_group_statistics()

    def handle_mobiles_user(self, now_date):
        """手机用户
        到期时间不为null 并且<当前日期，说明到期，需要减去记录数量
        到期日期为null 添加时没有累加，因此这里不需要减
        """

        # 日期到期，但状态未维护的
        records = MobileUserRecord.objects.filter(
            Q(group_5g_coin_exp_date__lt=now_date, group_5g_coin_is_exp=False) |
            Q(lt_exp_date__lt=now_date, lt_is_exp=False) |
            Q(dx_exp_date__lt=now_date, dx_is_exp=False)
        )

        for record in records:
            statistics, _ = GroupInformationStatistics.objects.get_or_create(group_id=record.group_id)
            if record.group_5g_coin_exp_date and record.group_5g_coin_exp_date < now_date:
                statistics.our_net_user_cnt -= record.our_net_users
                statistics.wiring_box_cnt -= record.wiring_box_penetration
                statistics.joint_pay_user_cnt -= record.joint_pay_users
                statistics.group_5g_coin_user_cnt -= record.our_net_5g_coin_users
                record.group_5g_coin_is_exp = True
            if record.lt_exp_date and record.lt_exp_date < now_date:
                statistics.lt_user_cnt -= record.lt_users
                statistics.lt_contract_gift_user_cnt -= record.lt_contract_gift_users
                record.lt_is_exp = True
            if record.dx_exp_date and record.dx_exp_date < now_date:
                statistics.dx_user_cnt -= record.dx_users
                statistics.dx_contract_gift_user_cnt -= record.dx_contract_gift_users
                record.dx_is_exp = True

            # 更新状态，避免重复处理
            record.save(update_fields=('group_5g_coin_is_exp', 'lt_is_exp', 'dx_is_exp', 'update_time'))
            statistics.save()

    def handle_special_line_user(self, now_date):
        """处理专线
        到期时间不为null 并且<当前日期，说明到期，需要减去记录数量
        到期日期为null 添加时没有累加，因此这里不需要减
        """
        records = SpecialLineManagement.objects.filter(
            Q(data_line_expiry__lt=now_date, data_line_is_exp=False) |
            Q(internet_line_expiry__lt=now_date, internet_line_is_exp=False) |
            Q(lt_expiry__lt=now_date, lt_is_exp=False) |
            Q(dx_expiry__lt=now_date, dx_is_exp=False) |
            Q(gd_expiry__lt=now_date, gd_is_exp=False)
        )
        for record in records:
            statistics, _ = GroupInformationStatistics.objects.get_or_create(group_id=record.group_id)
            # 数据专线：更新 数据专线条数、数据专线合计月收入、存量数据专线条数（我网、异网合计）
            if record.data_line_expiry and record.data_line_expiry < now_date:
                statistics.data_line_cnt -= record.data_line_count
                statistics.data_line_income -= record.data_line_income
                statistics.stock_data_line_cnt -= record.data_line_count
                record.data_line_is_exp = True
            # 互联网专线：更新 互联网专线条数、互联网专线合计月收入、存量互联网专线条数（我网、异网合计）
            if record.internet_line_expiry and record.internet_line_expiry < now_date:
                statistics.internet_line_cnt -= record.internet_line_count
                statistics.internet_line_income -= record.internet_line_income
                statistics.stock_internet_line_cnt -= record.internet_line_count
                record.internet_line_is_exp = True
            # LT专线：更新  LT数据专线条数、LT互联网专线条数、存量数据专线条数、存量互联网专线条数、异网专线合计月收入
            if record.lt_expiry and record.lt_expiry < now_date:
                statistics.lt_data_line_cnt -= record.lt_data_count
                statistics.lt_internet_line_cnt -= record.lt_internet_count
                statistics.other_net_income -= record.lt_data_fee + record.lt_internet_fee
                statistics.stock_data_line_cnt -= record.lt_data_count
                statistics.stock_internet_line_cnt -= record.lt_internet_count
                record.lt_is_exp = True
            # DX专线：更新  DX数据专线条数、DX互联网专线条数、存量数据专线条数、存量互联网专线条数、异网专线合计月收入
            if record.dx_expiry and record.dx_expiry < now_date:
                statistics.dx_data_line_cnt -= record.dx_data_count
                statistics.dx_internet_line_cnt -= record.dx_internet_count
                statistics.other_net_income -= record.dx_data_fee + record.dx_internet_fee
                statistics.stock_data_line_cnt -= record.dx_data_count
                statistics.stock_internet_line_cnt -= record.dx_internet_count
                record.dx_is_exp = True
            # GD专线：更新  GD数据专线条数、GD互联网专线条数、存量数据专线条数、存量互联网专线条数、异网专线合计月收入
            if record.gd_expiry and record.gd_expiry < now_date:
                statistics.gd_data_line_cnt -= record.gd_data_count
                statistics.gd_internet_line_cnt -= record.gd_internet_count
                statistics.other_net_income -= record.gd_data_fee + record.gd_internet_fee
                statistics.stock_data_line_cnt -= record.gd_data_count
                statistics.stock_internet_line_cnt -= record.gd_internet_count
                record.gd_is_exp = True

            # 更新状态，避免重复处理
            record.save(update_fields=('update_time', 'data_line_is_exp', 'internet_line_is_exp',
                                       'lt_is_exp', 'dx_is_exp', 'gd_is_exp'))
            statistics.save()

    def handle_mobiles_user_sql(self):
        self.update_user_phone_exp()
        self.update_user_phone_counts()

    def update_user_phone_exp(self):
        try:
            with connection.cursor() as cursor:
                # 构建 SQL 语句
                sql = """
                         UPDATE mobile_user_record
                         SET 
                             lt_is_exp = CASE 
                                 WHEN lt_exp_date < CURRENT_DATE THEN false 
                                 ELSE lt_is_exp 
                             END,
                             dx_is_exp = CASE 
                                 WHEN dx_exp_date < CURRENT_DATE THEN false 
                                 ELSE dx_is_exp 
                             END,
                             group_5g_coin_is_exp = CASE 
                                 WHEN group_5g_coin_exp_date < CURRENT_DATE THEN false 
                                 ELSE group_5g_coin_is_exp 
                             END
                         WHERE 
                             (lt_exp_date < CURRENT_DATE OR dx_exp_date < CURRENT_DATE OR group_5g_coin_exp_date < CURRENT_DATE);
                         """
                # 执行 SQL 语句
                cursor.execute(sql)
        except Exception as e:
            print(f"执行 SQL 语句时出错: {e}")

    def update_user_phone_counts(self):
        try:
            with connection.cursor() as cursor:
                # 构建 SQL 语句
                sql = f"""
                      UPDATE group_information_statistics
                      SET 
                          lt_user_cnt = subquery_lt.total_lt_users,
                          lt_contract_gift_user_cnt = subquery_lt.total_lt_contract_gift_users,
                          dx_user_cnt = subquery_dx.total_dx_users,
                          dx_contract_gift_user_cnt = subquery_dx.total_dx_contract_gift_users,
                          our_net_user_cnt = subquery_our_net.total_our_net_users,
                          wiring_box_cnt = subquery_our_net.total_wiring_box_penetration,
                          joint_pay_user_cnt = subquery_our_net.total_joint_pay_users,
                          group_5g_coin_user_cnt = subquery_our_net.total_our_net_5g_coin_users
                      FROM (
                          -- 统计 lt_users 和 lt_contract_gift_users 总和
                          SELECT 
                              group_id,
                              SUM(lt_users) as total_lt_users,
                              SUM(lt_contract_gift_users) as total_lt_contract_gift_users
                          FROM mobile_user_record
                          WHERE lt_is_exp = TRUE
                          GROUP BY group_id
                      ) AS subquery_lt,
                      (
                          -- 统计 dx_users 和 dx_contract_gift_users 总和
                          SELECT 
                              group_id,
                              SUM(dx_users) as total_dx_users,
                              SUM(dx_contract_gift_users) as total_dx_contract_gift_users
                          FROM mobile_user_record
                          WHERE dx_is_exp = TRUE
                          GROUP BY group_id
                      ) AS subquery_dx,
                      (
                          -- 统计 our_net_users 及其他字段总和
                          SELECT 
                              group_id,
                              SUM(our_net_users) as total_our_net_users,
                              SUM(wiring_box_penetration) as total_wiring_box_penetration,
                              SUM(joint_pay_users) as total_joint_pay_users,
                              SUM(our_net_5g_coin_users) as total_our_net_5g_coin_users
                          FROM mobile_user_record
                          WHERE group_5g_coin_is_exp =  TRUE 
                          GROUP BY group_id
                      ) AS subquery_our_net
                      WHERE 
                          group_information_statistics.group_id = subquery_lt.group_id
                          AND group_information_statistics.group_id = subquery_dx.group_id
                          AND group_information_statistics.group_id = subquery_our_net.group_id;
                      """
                # 执行 SQL 语句
                cursor.execute(sql)
        except Exception as e:
            print(f"执行 SQL 语句时出错: {e}")

    def checkGroupStatistics(self):
        try:
            # 构建 SQL 语句
            sql = """
                        INSERT INTO group_information_statistics (
                            group_id, 
                            our_net_user_cnt, 
                            wiring_box_cnt, 
                            joint_pay_user_cnt
                        )
                        SELECT 
                            id,
                            0,
                            0,
                            0 
                        FROM 
                            group_customer_info 
                        WHERE 
                            id NOT IN (
                                SELECT group_id 
                                FROM group_information_statistics 
                                WHERE group_id IS NOT NULL
                            )
                    """
            # 执行 SQL 语句
            with connection.cursor() as cursor:
                cursor.execute(sql)
        except Exception as e:
            # 使用日志记录错误信息
            print(f"执行 SQL 语句时出错: {e}")

    def handle_special_line_sql(self):
        self.update_special_line_exp()
        self.update_special_line_counts()

    def update_special_line_exp(self):
        try:
            with connection.cursor() as cursor:
                sql = f"""
                    UPDATE special_line_management
                    SET 
                        internet_line_is_exp = CASE 
                            WHEN internet_line_expiry < CURRENT_DATE THEN false 
                            ELSE internet_line_is_exp 
                        END,
                        lt_is_exp = CASE 
                            WHEN lt_expiry < CURRENT_DATE THEN false 
                            ELSE lt_is_exp 
                        END,
                        dx_is_exp = CASE 
                            WHEN dx_expiry < CURRENT_DATE THEN false 
                            ELSE dx_is_exp 
                        END,
                        gd_is_exp = CASE 
                            WHEN gd_expiry < CURRENT_DATE THEN false 
                            ELSE gd_is_exp 
                        END
                    WHERE 
                        (internet_line_expiry < CURRENT_DATE OR 
                        lt_expiry < CURRENT_DATE OR 
                        dx_expiry < CURRENT_DATE OR 
                        gd_expiry < CURRENT_DATE );
                    """
                cursor.execute(sql)
        except Exception as e:
            print(f"执行 SQL 语句时出错: {e}")

        pass

    def update_special_line_counts(self):
        try:
            with connection.cursor() as cursor:
                sql = """
                    UPDATE group_information_statistics
                    SET 
                        data_line_cnt = subquery_data.total_data_line_count,
                        data_line_income = subquery_data.total_data_line_income,
                        internet_line_cnt = subquery_internet.total_internet_line_count,
                        internet_line_income = subquery_internet.total_internet_line_income,
                        lt_data_line_cnt = subquery_lt.total_lt_data_count,
                        lt_internet_line_cnt = subquery_lt.total_lt_internet_count,
                        dx_data_line_cnt = subquery_dx.total_dx_data_count,
                        dx_internet_line_cnt = subquery_dx.total_dx_internet_count,
                        gd_data_line_cnt = subquery_gd.total_gd_data_count,
                        gd_internet_line_cnt = subquery_gd.total_gd_internet_count
                    FROM (
                        -- 统计 data_line 相关数据
                        SELECT 
                            group_id,
                            SUM(data_line_count) AS total_data_line_count,
                            SUM(data_line_income) AS total_data_line_income
                        FROM 
                            special_line_management
                        WHERE 
                            data_line_is_exp = TRUE
                        GROUP BY 
                            group_id
                    ) AS subquery_data,
                    (
                        -- 统计 internet_line 相关数据
                        SELECT 
                            group_id,
                            SUM(internet_line_count) AS total_internet_line_count,
                            SUM(internet_line_income) AS total_internet_line_income
                        FROM 
                            special_line_management
                        WHERE 
                            internet_line_is_exp = TRUE
                        GROUP BY 
                            group_id
                    ) AS subquery_internet,
                    (
                        -- 统计 LT 相关数据
                        SELECT 
                            group_id,
                            SUM(lt_data_count) AS total_lt_data_count,
                            SUM(lt_internet_count) AS total_lt_internet_count
                        FROM 
                            special_line_management
                        WHERE 
                            lt_is_exp = TRUE
                        GROUP BY 
                            group_id
                    ) AS subquery_lt,
                    (
                        -- 统计 DX 相关数据
                        SELECT 
                            group_id,
                            SUM(dx_data_count) AS total_dx_data_count,
                            SUM(dx_internet_count) AS total_dx_internet_count
                        FROM 
                            special_line_management
                        WHERE 
                            dx_is_exp = TRUE
                        GROUP BY 
                            group_id
                    ) AS subquery_dx,
                    (
                        -- 统计 GD 相关数据
                        SELECT 
                            group_id,
                            SUM(gd_data_count) AS total_gd_data_count,
                            SUM(gd_internet_count) AS total_gd_internet_count
                        FROM 
                            special_line_management
                        WHERE 
                            gd_is_exp = TRUE
                        GROUP BY 
                            group_id
                    ) AS subquery_gd
                    WHERE 
                        group_information_statistics.group_id = subquery_data.group_id
                        AND group_information_statistics.group_id = subquery_internet.group_id
                        AND group_information_statistics.group_id = subquery_lt.group_id
                        AND group_information_statistics.group_id = subquery_dx.group_id
                        AND group_information_statistics.group_id = subquery_gd.group_id;
                """
                cursor.execute(sql)
        except Exception as e:
            print(f"执行 SQL 语句时出错: {e}")

    def handle_group_statistics(self):
        try:
            with connection.cursor() as cursor:
                sql = """
                    UPDATE group_information_statistics
                    SET 
                        stock_data_line_cnt = data_line_cnt + lt_data_line_cnt + dx_data_line_cnt + gd_data_line_cnt,
                        stock_internet_line_cnt = internet_line_cnt + lt_internet_line_cnt + dx_internet_line_cnt + gd_internet_line_cnt;
                """
                cursor.execute(sql)
        except Exception as e:
            print(f"执行 SQL 语句时出错: {e}")
